package pe.com.bbva.pct.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.springframework.stereotype.Repository;

import pe.com.bbva.pct.dao.ConexionJndiDAO;
import pe.com.bbva.pct.dto.DetalleFuenteDTO;

@Repository("conexionJndiDao")
public class ConexionJndiDAOImpl implements ConexionJndiDAO{

	
	
	public List<String> obtenerCampos(String jndi, String query, int tipo) throws Exception{
		 List<String> listaCampos = new ArrayList<String>();
		 Connection connection = null;
		try {
			InitialContext context = new InitialContext();
		    DataSource dataSource = (DataSource) context.lookup(jndi);
		    connection = dataSource.getConnection();
			
		    if (connection != null) {
		        if(tipo==1){
		        	
		        	String sql = query;
			        
			        PreparedStatement statement = connection.prepareStatement(sql);
			        ResultSet rs = statement.executeQuery();

			      
			        	ResultSetMetaData rsmd = rs.getMetaData();
			        	int columnCount = rsmd.getColumnCount();

			        	// The column count starts from 1
			        	for (int i = 1; i < columnCount + 1; i++ ) {
			        	  String name = rsmd.getColumnName(i);
			        	  listaCampos.add(name);
			        	}
			          
			        
		        }else if(tipo==2){
		        	
		        	String sql = "select distinct(column_name) from all_tab_columns where table_name = '"+query+"'";
			        
			        PreparedStatement statement = connection.prepareStatement(sql);
			        ResultSet rs = statement.executeQuery();

			        while (rs.next()) {
			         
			        	
			        	  listaCampos.add(rs.getString(1));
			        }
		        }
		    	
		        connection.close();
		    }   
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listaCampos;
	}
	
	
	public List<Object[]> obtenerRegistro(String jndi, String query)throws Exception{
		 List<Object[]> records=new ArrayList<Object[]>();
		 Connection connection = null;
		try {
					InitialContext context = new InitialContext();
				    DataSource dataSource = (DataSource) context.lookup(jndi);
				    connection = dataSource.getConnection();
					
				        	
				        	String sql = query;
					        
					        PreparedStatement statement = connection.prepareStatement(sql);
					        ResultSet rs = statement.executeQuery();

					       
					        //List<Object[]> records=new ArrayList<Object[]>();
					        while(rs.next()){
					            int cols = rs.getMetaData().getColumnCount();
					            Object[] arr = new Object[cols];
					            for(int i=0; i<cols; i++){
					              arr[i] = rs.getObject(i+1);
					            }
					            records.add(arr);
					        }
					        
					       
					        	

				        
				    	
				        connection.close();
				      
		} catch (Exception e) {
			e.printStackTrace();
		}
		return records;
	}
	
	
	public boolean insertarRegistro(List<DetalleFuenteDTO> listaDetalleFuenteDTO, String jndi, String tabla) throws Exception{
		boolean result=false;
		Connection connection = null;
		try {
			 
							InitialContext context = new InitialContext();
						    DataSource dataSource = (DataSource) context.lookup(jndi);
						    connection = dataSource.getConnection();
						    StringBuffer query = new StringBuffer();
						    query.append("INSERT INTO "+tabla+" (");
						    for (DetalleFuenteDTO item : listaDetalleFuenteDTO) {
						        query.append(item.getNombreCampo()+", ");
						    }
						    StringBuffer query2 = new StringBuffer();
						    query2.append( query.toString().substring(0, query.length()-2));
						    query2.append(") VALUES (");
						    
						    for (DetalleFuenteDTO item : listaDetalleFuenteDTO) {
						    	query2.append(item.getValor()+", ");
						    }
						    StringBuffer query3 = new StringBuffer();
						    query3.append(query2.toString().substring(0, query2.length()-2));
						    
						    query3.append(")");
						    System.out.println(query3.toString());
						    PreparedStatement statement = connection.prepareStatement(query3.toString());
						    
						    statement.executeUpdate();
						    result=true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
		
	}
	
	public ArrayList<HashMap<String, Object>> obtenerConsulta (String jndi, String query) throws Exception{
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		List<Object[]> records=new ArrayList<Object[]>();
		 Connection connection = null;
		try {
					InitialContext context = new InitialContext();
				    DataSource dataSource = (DataSource) context.lookup(jndi);
				    connection = dataSource.getConnection();
					
				        	
				        	String sql = query;
					        
					        PreparedStatement statement = connection.prepareStatement(sql);
					        ResultSet rs = statement.executeQuery();

					        ResultSetMetaData md = rs.getMetaData();
					        int columns = md.getColumnCount();
					        list = new ArrayList(50);
					        while (rs.next()){
					           HashMap row = new HashMap(columns);
					           for(int i=1; i<=columns; ++i){           
					            row.put(md.getColumnName(i),rs.getObject(i));
					           }
					            list.add(row);
					        }
					        
					       
					        	

				        
				    	
				        connection.close();
				      
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
}
